<?php if (!defined('TL_ROOT')) die('You can not access this file directly!');

/**
 * TYPOlight webCMS
 * Copyright (C) 2005-2009 Leo Feyer
 *
 * This program is free software: you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation, either
 * version 2.1 of the License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * Lesser General Public License for more details.
 * 
 * You should have received a copy of the GNU Lesser General Public
 * License along with this program. If not, please visit the Free
 * Software Foundation website at http://www.gnu.org/licenses/.
 *
 * PHP version 5
 * @copyright  Leo Feyer 2005-2009
 * @author     Leo Feyer <leo@typolight.org>
 * @package    Backend
 * @license    LGPL
 * @filesource
 */


/**
 * Class ModuleMaintenance
 *
 * Back end module "maintenance".
 * @copyright  Leo Feyer 2005-2009
 * @author     Leo Feyer <leo@typolight.org>
 * @package    Controller
 */
class ModuleOrderList extends BackendModule
{

	/**
	 * Template
	 * @var string
	 */
	protected $strTemplate = 'be_orderlist';
	
	/**
	 * pase ajax - 交互属性
	 */
	private function paseAjax()
	{
		switch ($this->Input->post('action'))
		{
			case 'loadCatalogSize':
				$obj_catalog_attributes = $this->Database->prepare("SELECT ca_size.id, ca_size.size  
																FROM `tl_catalog_attribute` AS attr
																INNER JOIN `catalog_size` AS ca_size ON ca_size.`id`=attr.`id_size` 
																WHERE attr.`id_catalog`=? AND attr.`id_color`=?")
													->execute($this->Input->post('id_catalog'),$this->Input->post('id_colour'));
							
				if ($obj_catalog_attributes->numRows < 1)
				{
					echo "Out of stock. Please send us an email if you would like to back order.";exit;
				}
				else
				{
					$str_html = '<select name="item_size['.$this->Input->post('id_catalog').'_'.$this->Input->post('detail_id').']" class="skin_size"><option' . ((!$this->Input->post('default_size')) ? ' selected="selected"' : '') .' value="">- SELECT SIZE -</option>';
					$index = 0;
					while ($obj_catalog_attributes->next())
					{
						$str_html .= "<option" . (($this->Input->post('default_size') == $obj_catalog_attributes->id) ? ' selected="selected"' : '') . " value=\"".$obj_catalog_attributes->id."\">".$obj_catalog_attributes->size."</option>";
					}
					$str_html .= "</select>";
					
					echo $str_html;exit;
				}
				break;
			case 'loadCatalogQuantity':
				$obj_catalog_attributes = $this->Database->prepare("SELECT attr.`catalog_stock` 
																FROM `tl_catalog_attribute` AS attr 
																WHERE attr.`id_catalog`=? AND attr.`id_color`=? AND attr.`id_size`=?")
													->execute($this->Input->post('id_catalog'),$this->Input->post('id_colour'),$this->Input->post('id_size'));
							
				if ($obj_catalog_attributes->numRows < 1)
				{
					echo "Out of stock. Please send us an email if you would like to back order.";exit;
				}
				else
				{
					$str_html = '<select name="item_quantity['.$this->Input->post('id_catalog').'_'.$this->Input->post('detail_id').']" class="skin_quantity"><option' . ((!$this->Input->post('defalut_quantity')) ? ' selected="selected"' : '') .' value="">- SELECT QUANTITY -</option>';
					$index = 0;
					if ($obj_catalog_attributes->catalog_stock > 10)
						$total_quantity = 10;
					else
						$total_quantity = $obj_catalog_attributes->catalog_stock;
					for ($i=1;$i<=$total_quantity;$i++)
					{
						$str_html .= "<option" . (($this->Input->post('defalut_quantity') == $i) ? ' selected="selected"' : '') . " value=\"".$i."\">".$i."</option>";
					}
					$str_html .= "</select>";
					
					echo $str_html;exit;
				}
				break;
			case 'loadcatalog':
				$obj_catalog = $this->Database->prepare("SELECT cata.`id` AS id_catalog, cata.`name` AS cata_name, cata.`category` AS id_category, cata.`skuid`, cata.`price`, cata.`is_wrapping`, cata_co.`title` AS color_name, cata_co.`id` AS id_color  
														FROM `product_catalog` AS cata 
														INNER JOIN `tl_catalog_attribute` AS attr ON attr.`id_catalog`=cata.`id` 
														INNER JOIN `catalog_colour` AS cata_co ON cata_co.`id`=attr.`id_color` 
														WHERE cata.`skuid`=? 
														GROUP BY attr.`id_color`")
											->execute($this->Input->post('skuid'));
				if ($obj_catalog->numRows < 1)
				{
					echo 'Product is not exisits!';exit;
				}
				$index = 0;
				$arr_catalog = array();
				while ($obj_catalog->next())
				{
					if ($index == 0)
					{
						$imgPath = "daft_files/catalog/products/" . $obj_catalog->skuid . "/" . $obj_catalog->color_name . "/1.jpg";
						
						$arr_catalog[$index] = array(
							'id_catalog'=>$obj_catalog->id_catalog,
							'id_category'=>$obj_catalog->id_category,
							'product_name'=>$obj_catalog->cata_name,
							'skuid'=>$obj_catalog->skuid,
							'price'=>$obj_catalog->price,
							'id_color'=>$obj_catalog->id_color,
							'imagePath'=>$imgPath,
							'is_wrapping'=>$obj_catalog->is_wrapping,						
						);
					}
					
					$arr_catalog[0]['colors'][] = array(
													'color_name'=>$obj_catalog->color_name,
													'id_color'=>$obj_catalog->id_color,
												);
					$index++;
				}
				$obj_attributes = $this->Database->prepare("SELECT cata_size.`id`, cata_size.`size`, attr.`catalog_stock` 
															FROM `tl_catalog_attribute` AS attr
															INNER JOIN `catalog_size` AS cata_size ON cata_size.`id`=attr.`id_size`
															WHERE attr.`id_catalog`=? AND attr.`id_color`=?")
												->execute($arr_catalog[0]['id_catalog'],$arr_catalog[0]['id_color']);
				if ($obj_attributes->numRows >= 1)
				{
					while ($obj_attributes->next())
						$arr_catalog[0]['sizes'][] = array(
							'id_size'=>$obj_attributes->id,
							'size'=>$obj_attributes->size,
							'catalog_stock'=>$obj_attributes->catalog_stock,
						);
					$arr_catalog[0]['id_size'] = $arr_catalog[0]['sizes'][0]['id_size'];
					$arr_catalog[0]['catalog_stock'] = $arr_catalog[0]['sizes'][0]['catalog_stock'];
				}
				
				$str_html = '<input type="hidden" class="id_catalog" value="' . $arr_catalog[0]['id_catalog'] . '" />
							 <input type="hidden" class="id_odr_detail_id" value="0" />
							 <tr>';
				$str_html .= '<td rowspan="2" class="td_pic"><a href="' . $this->Environment->base . 'product.html?category=' . $arr_catalog[0]['id_category'] . '&id=' . $arr_catalog[0]['id_catalog'] . '&id_colour=' . $arr_catalog[0]['id_color'] . '&id_size=' . $arr_catalog[0]['id_size'] . '"><img src="' . $this->Environment->base . 'image.php?src=' . $arr_catalog[0]['imagePath']. '&width=79&height=79" alt="" /></a></td>';
				$str_html .= '<td class="td_info">
							<h4><a href="' . $this->Environment->base . 'product.html?category=' . $arr_catalog[0]['id_category'] . '&id=' . $arr_catalog[0]['id_catalog'] . '&id_colour=' . $arr_catalog[0]['id_color'] . '&id_size=' . $arr_catalog[0]['id_size'] . '">' . $arr_catalog[0]['product_name'] . '</a></h4>
							<small>SKU <span style="color:red;">#' . $arr_catalog[0]['skuid'] . '</span></small><br />
							<small>RMB <label>' . number_format($arr_catalog[0]['odrPrice'], 2, '.', '') . '</label></small></td>';
				$str_html .= '<td rowspan="2" class="td_skin">
							<div class="skin">
								<p>
									<label><strong>COLOR</strong></label>
									<span class="skin_value">
										<select class="skin_colour" name="item_colour[' . $arr_catalog[0]['id_catalog'] . '_0]">';
				
				foreach ($arr_catalog[0]['colors'] as $key=>$arr_colours)
				{
					$str_html .= '<option value="' . $arr_colours['id_color'] . '">' . $arr_colours['color_name'] . '</option>';
				}
				$str_html .= '</select>
									</span>
								</p>
								<p>
									<label><strong>SIZE</strong></label>
									<span class="skin_value">										
										<input type="hidden" class="default_size" value="' . ($arr_catalog[0]['id_size'] ? $arr_catalog[0]['id_size'] : 0) . '" />
										<select class="skin_size" name="item_size[' . $arr_catalog[0]['id_catalog'] . '_0]">
											<option' . ((!$arr_catalog[0]['id_size']) ? ' selected="selected"' : '') . ' value="">- SELECT SIZE -</option>';
				
				foreach ($arr_catalog[0]['sizes'] as $key=>$arr_sizes)
				{
					$str_html .= '<option' . (($arr_catalog[0]['id_size'] == $arr_sizes['id_size']) ? ' selected="selected"' : '') . ' value="' . $arr_sizes['id_size'] . '">' . $arr_sizes['size'] . '</option>';
				}
				$str_html .= '</select>
									</span>
								</p>
								<p>
									<label><strong>QUANTITY</strong></label>
									<span class="skin_value">
									<select class="skin_quantity" name="item_quantity[' . $arr_catalog[0]['id_catalog'] . '_0]">';
				if ($arr_catalog[0]['catalog_stock'] > 10)
					$total_quantity = 10;
				else
					$total_quantity = intval($arr_catalog[0]['catalog_stock']);
					
				for ($i=1;$i<=$total_quantity;$i++)
				{
					$str_html .= '<option' . (($i == 1) ? ' selected="selected"' : '') . ' value="' . $i . '">' . $i . '</option>';
				}
				$str_html .= '</select>
									<input type="hidden" class="default_quantity" value="1" />
									</span>
								</p>
							</div>
						</td>						
						<td rowspan="2" class="unit_price">
							<label><strong>UNIT PRICE</strong></label>
							<input type="text" name="item_price[' . $arr_catalog[0]['id_catalog'] . '_0]" value="' . number_format($arr_catalog[0]['price'], 2, '.', '') . '" />
						</td>
						<td rowspan="2" class="total_item"><strong>RMB <label>' . number_format($arr_catalog[0]['price'], 2, '.', '') . '</label></strong></td>
						<td rowspan="2" class="del_columns"><a href="javascript:void(0);" title="delete" class="odr_delete"><img src="system/themes/default/images/delete.gif" alt="delete" /></a></td>';
				$str_html .= '</tr>';
				echo $str_html;exit;
				break;
			case 'update_order_item':
				$arr_item_colour = $_POST['item_colour'];
				$arr_item_price = $_POST['item_price'];
				$arr_item_quantity = $_POST['item_quantity'];
				$arr_item_size = $_POST['item_size'];
				/** 存入属性 **/
				$subCatalog = 0;
				$id_catalog = 0;
				$id_order_detail = 0;
				foreach ($arr_item_colour as $key=>$item_colour)
				{
					$arr_keys = explode("_", $key);
					$id_catalog = $arr_keys[0];
					$id_order_detail = $arr_keys[1];
					/** Get colour **/
					$obj_colour = $this->Database->prepare("SELECT `title` FROM `catalog_colour` WHERE `id`=?")
												->execute($item_colour);
					/** Get Size **/
					$obj_size = $this->Database->prepare("SELECT `size` FROM `catalog_size` WHERE `id`=?")
												->execute($arr_item_size[$key]);
					$str_catalog_attr = "Color:" . $obj_colour->title . "\r\n" . 'Size:' . $obj_size->size;
					/** Catalog information **/
					$obj_catalog = $this->Database->prepare("SELECT * FROM `product_catalog` WHERE `id`=?")
												->execute($id_catalog);
					/** 插入order_detail **/
					if (!$id_order_detail)
					{
						$is_update = $this->Database->prepare("INSERT INTO `tl_order_detail` (`pid`,`product_catalog_id`,`tstamp`,`product_attribute`,`product_name`,`product_skuid`,`product_quantity`,`product_price`,`product_reference`) VALUES (?,?,?,?,?,?,?,?,?)")
										->execute($this->Input->get('id'),$id_catalog,time(),$str_catalog_attr,$obj_catalog->name,$obj_catalog->skuid,$arr_item_quantity[$key],$arr_item_price[$key],$obj_catalog->description);
					}
					else
					{
						$is_update = $this->Database->prepare("UPDATE `tl_order_detail` SET `product_attribute`=?,`product_quantity`=?,`product_price`=? WHERE `id`=?")
												->execute($str_catalog_attr,$arr_item_quantity[$key],$arr_item_price[$key],$id_order_detail);
					}
					
					$subCatalog += intval($arr_item_price[$key]) * intval($arr_item_quantity[$key]);
				}
				
				/** Get order information **/
				$obj_order = $this->Database->prepare("SELECT * FROM `tl_orders` WHERE `id`=?")
												->execute($this->Input->get('id'));
				$total_catolog = $subCatalog;
				$total_paid = $total_catolog + $obj_order->total_shipping + $obj_order->total_wrapping;
				$total_real_paid = $total_paid - $obj_order->total_discounts;
				$is_update = $obj_order = $this->Database->prepare("UPDATE `tl_orders` SET `total_paid`=?,`total_paid_real`=?,`total_catolog`=? WHERE `id`=?")
												->execute($total_paid,$total_real_paid,$total_catolog,$this->Input->get('id'));
												
				if ($is_update->affectedRows){
					include_once(TL_ROOT.'/templates/validationPaypal.php');
					$obj_send_email = new ValidationPaypal();
					$obj_send_email->send_email($this->Input->get('id'), 'Your order has been changed.');
					echo '[{"res":"succ","msg":"Update successfully!"}]';exit;
				}else{
					echo '[{"res":"fail","msg":"No changed!"}]';exit;
				}
				break;
			case 'update_order_comments':
				$comments = $_POST['odr_status_comments'];
				$id_order = $_POST['id_order'];
				$is_update = $this->Database->prepare("UPDATE `tl_orders` SET `odr_status_comments`=? WHERE `id`=?")
											->execute($comments,$id_order);
				break;
		}
	}

	/**
	 * Generate module
	 */
	protected function compile()
	{
		$this->loadLanguageFile('be_orderlist');		
		$this->obj_currency = new Currency();
		// ajax the add new item of order or change the order information.
		if ($this->Input->post('action'))
		{
			$this->paseAjax();
			return;
		}
		if (!$this->Input->get('act') || $this->Input->get('act') == 'list')
		{
			if ($this->Input->post('FORM_SUBMIT') == 'tl_order_status_change')
			{
				// Change the order status.
				if ($this->Input->post('order_status') && $this->Input->post('order_status') == '6')
				{
					$objOrders = $this->Database->prepare("SELECT odd.*, odr.`orderno`, me.`firstname`, me.`lastname`, me.`email`  
															FROM `tl_orders` AS odr
															LEFT JOIN `tl_order_detail` AS odd ON odd.`pid`=odr.`id`
															LEFT JOIN `tl_member` AS me ON me.`id`=odr.`id_member`
															WHERE odr.`id`=?")
											->execute($this->Input->post('id_order'), time(), time());
					
					if ($objOrders->numRows >= 1)
					{
						$catalogAttr = "";
						$arrCatalogAttr = array();
						$arrColors = array();
						$arrSizes = array();
						
						while ($objOrders->next())
						{
							$orderNo = $objOrders->orderno;
							$memberName = $objOrders->firstname . ' ' . $objOrders->lastname;
							$memberEmailAddr = $objOrders->email;
							$catalogAttr = $objOrders->product_attribute;
							$arrCatalogAttr = explode("\n", $catalogAttr);
							$arrColors = explode(":", $arrCatalogAttr[0]);
							$arrSizes = explode(":", $arrCatalogAttr[1]);
							
							$objColors =  $this->Database->prepare("SELECT `id`
																	FROM `catalog_colour`
																	WHERE `title`=?")
														->execute($arrColors[1]);
							$objSizes =  $this->Database->prepare("SELECT `id`
																	FROM `catalog_size`
																	WHERE `size`=?")
														->execute($arrSizes[1]);
							
							if ($objColors->numRows >= 1 && $objSizes->numRows >= 1)
							{
								$objCatalogAttr = $this->Database->prepare("UPDATE `tl_catalog_attribute` SET `catalog_stock`=`catalog_stock`+? WHERE `id_catalog`=? AND `id_color`=? AND `id_size`=?")
																->execute($objOrders->product_quantity, $objOrders->product_catalog_id, $objColors->id, $objSizes->id);
							}
						}
						
						$email = new Email();
						$email->subject = "Your order #" . $orderNo . " has been cancelled";
						$email->from = $GLOBALS['TL_CONFIG']['adminEmail'];
						$email->fromName = "Daftshopping.com";
						$email->replyTo("34n@gmail.com");
						
						$strHtml = "Hi " . $memberName . ",<br /><br />";
						$strHtml .= "Your order #" . $orderNo . " has been cancelled.";
						$strHtml .= "<br /><br />";
						$strHtml .= 'We hope to hear from you again soon. Have a great day!<br /><br />';
						$strHtml .= 'Kindest regards,<br />Team at Daftshopping';
						
						$email->html = $strHtml;
						//$email->sendTo("songbin_82@126.com");								
						$email->sendTo($memberEmailAddr);
					}
					
					if ($this->Input->post('order_status') == '3')
						$objStatus = $this->Database->prepare("UPDATE `tl_orders` SET `odr_status`=?, `odr_can_com`=?, `date_upd`=?, `date_ship`=? WHERE `id`=?")
											->execute($this->Input->post('order_status'), $this->Input->post('odr_can_com'), date("Y-m-d H:i:s"), date("Y-m-d H:i:s"), $this->Input->post('id_order'));
					else
						$objStatus = $this->Database->prepare("UPDATE `tl_orders` SET `odr_status`=?, `odr_can_com`=?, `date_upd`=? WHERE `id`=?")
											->execute($this->Input->post('order_status'), $this->Input->post('odr_can_com'), date("Y-m-d H:i:s"), $this->Input->post('id_order'));
				}
				else
				{
					if ($this->Input->post('order_status') == '3')
						$objStatus = $this->Database->prepare("UPDATE `tl_orders` SET `odr_status`=?, `date_upd`=?, `date_ship`=? WHERE `id`=?")
											->execute($this->Input->post('order_status'), date("Y-m-d H:i:s"), date("Y-m-d H:i:s"), $this->Input->post('id_order'));
					else
						$objStatus = $this->Database->prepare("UPDATE `tl_orders` SET `odr_status`=?, `date_upd`=? WHERE `id`=?")
											->execute($this->Input->post('order_status'), date("Y-m-d H:i:s"), $this->Input->post('id_order'));
				}
				
				// change the order history - log.
				$objStatus = $this->Database->prepare("UPDATE `tl_order_history` SET `id_order_state`=?, `date_add`=? WHERE `id_order`=?")
											->execute($this->Input->post('order_status'), date("Y-m-d H:i:s"), $this->Input->post('id_order'));				
				$this->redirect("typolight/main.php?do=orderlist&act=detail&id=" . $this->Input->post('id_order'));				
			}
			else 
			{
				// Generate order list.
				$this->Template->orders = $this->generateOrderList();
				//$this->Template->limitValue = ($this->Input->post('tl_limit')) ? $this->Input->post('tl_limit') : "0,30";
				$this->Template->limitValue = ($this->Input->post('tl_limit')) ? $this->Input->post('tl_limit') : "all";
			}
		}
		else
		{
			switch ($this->Input->get('act'))
			{
				case 'detail':
					$this->Template = new BackendTemplate("be_orderdetail");
					$this->Template->orderDetails = $this->getOrderDetail($this->Input->get('id'));
					$this->Template->obj_currency = $this->obj_currency;
					$this->Template->limitValue = ($this->Input->post('tl_limit')) ? $this->Input->post('tl_limit') : "all";
					// Get the state, country and city.
					$this->loadLanguageFile('countries');
					include(TL_ROOT . '/system/config/countries.php');
		
					// Get the states.
					$arrStates = array();
					$objStates = $this->Database->prepare("SELECT * FROM `china_province` ORDER BY `province_en`")
												->execute(time());
					while ($objStates->next())
					{
						$arrStates[$objStates->id] = stripslashes($objStates->province_en);
					}
					
					// Get city.
					$arrCitys = array();			
					$objCitys = $this->Database->prepare("SELECT * FROM `china_city` ORDER BY `city_en`")
												->execute(time());
												
					while ($objCitys->next())
					{
						$arrCitys[$objCitys->id] = stripslashes($objCitys->city_en);
					}
					
					$this->Template->arrSates = $arrStates;
					$this->Template->arrCitys = $arrCitys;
					$this->Template->arrCountries = $countries;
					$this->Template->basePath = $this->Environment->base;
					break;
				case 'delete':
					// Delete the order list.
					$objOrderDetails = $this->Database->prepare("DELETE FROM `tl_order_detail` WHERE `pid`=?")
													->execute($this->Input->get('id'));
					$objOrder = $this->Database->prepare("DELETE FROM `tl_orders` WHERE `id`=?")
													->execute($this->Input->get('id'));
					
					$this->redirect("typolight/main.php?do=orderlist");
					break;
				case 'show':
					break;
				default:
					break;
			}
		}
	}
	
	/**
	 * Handle the html 
	 */
	protected function generateOrderList()
	{
		//$strLimit = "LIMIT 0,30";
		$strLimit = "";
		$strWhere = "WHERE 1";
		$cnt = 0;
		$arrWildcards = array();
		$arrValues = array();
		$intLength = 0;
		
		if ($this->Input->post('FORM_SUBMIT'))
		{
			if ($this->Input->post('tl_limit'))
				if ($this->Input->post('tl_limit') == 'all')
					$strLimit = "";
				else
					$strLimit = "LIMIT " . $this->Input->post('tl_limit');
			
			if ($this->Input->post('tl_order_search') && $this->Input->post('tl_search_value'))
			{
				switch ($this->Input->post('tl_order_search'))
				{
					case 'skuid':
						//$strWhere .= " AND cata.`skuid` = '" . $this->Input->post('tl_search_value') . "'";
						$arrWildcards[] = "cata.`skuid` = ?";
						$arrValues[] = $this->Input->post('tl_search_value');
						break;
					case 'id_member':
						//$strWhere = " AND me.`username` = '" . $this->Input->post('tl_search_value') . "'";
						$arrWildcards[] = "(me.`email` LIKE ? OR CONCAT(me.`firstname`, ' ', me.`lastname`) LIKE ?)";
						$intLength = count($arrValues);
						$arrValues[$intLength + 1] = '%' . $this->Input->post('tl_search_value') . '%';
						$arrValues[$intLength + 2] = '%' . $this->Input->post('tl_search_value') . '%';
						break;
					case 'orderno':
						//$strWhere = " AND odr.`orderno` = '" . $this->Input->post('tl_search_value') . "'";
						$arrWildcards[] = "odr.`orderno` = ?";
						$arrValues[] = $this->Input->post('tl_search_value');
						break;
					case 'payment':
						//$strWhere = " AND odr.`payment` = '" . $this->Input->post('tl_search_value') . "'";
						$arrWildcards[] = "odr.`payment` = ?";
						$arrValues[] = $this->Input->post('tl_search_value');
						break;
					default;
						break;
				}

				$this->Template->order_search = $this->Input->post('tl_order_search');
				$this->Template->search_value = $this->Input->post('tl_search_value');
			}
			
			if ($this->Input->post('tl_order_status') && $this->Input->post('tl_order_status') != "")
			{
				//$strWhere .= " AND odr.`odr_status` = '" . $this->Input->post('tl_order_status') . "'";
				$arrWildcards[] = "odr.`odr_status` = ?";
				$arrValues[] = $this->Input->post('tl_order_status');
				
				$this->Template->odr_status = $this->Input->post('tl_order_status');
			}			
		}
		
		$objOrdersCnt = $this->Database->prepare("SELECT odr.`id` 
												FROM `tl_orders` AS odr 
												LEFT JOIN `tl_member` AS me ON me.`id`=odr.`id_member` 
												LEFT JOIN `tl_order_detail` AS odd ON odd.`pid`=odr.`id`
												LEFT JOIN `product_catalog` AS cata ON odd.`product_catalog_id`=cata.`id` 
												" . $strWhere . (($arrWildcards) ? " AND " . implode(' AND ', $arrWildcards) : "") . " 
												GROUP BY odr.`id`
												ORDER BY odr.`date_add` DESC")
									->execute($arrValues, time(), time());
		
		if ($objOrdersCnt->numRows < 1)
			return;
		
		$cnt = $objOrdersCnt->numRows;
		$this->Template->orderCnt = $objOrdersCnt->numRows;
		
		$objOrders = $this->Database->prepare("SELECT odr.`id`, odr.`orderno`, IF(CONCAT(me.`firstname`, ' ', me.`lastname`)<>'', CONCAT(me.`firstname`, ' ', me.`lastname`), me.username) AS memberName, odr.`total_paid_real`, odr.`date_add`, (CASE odr.`odr_status` WHEN '1' THEN 'Payment pending' WHEN '2' THEN 'Preparation in progress' WHEN 3 THEN 'Shipping' WHEN 4 THEN 'Delivered' ELSE 'Cancelled' END) AS status, odr.`payment`, cur.iso_code  
												FROM `tl_orders` AS odr 
												LEFT JOIN `tl_member` AS me ON me.`id`=odr.`id_member` 
												LEFT JOIN `tl_order_detail` AS odd ON odd.`pid`=odr.`id`
												LEFT JOIN `product_catalog` AS cata ON odd.`product_catalog_id`=cata.`id` 
												LEFT JOIN tl_currency AS cur ON cur.id=odr.id_currency 
												" . $strWhere . (($arrWildcards) ? " AND " . implode(' AND ', $arrWildcards) : "") . " 
												GROUP BY odr.`id`
												ORDER BY odr.`date_add` DESC " . $strLimit)
									->execute($arrValues, time(), time());
		
		if ($objOrders->numRows < 1)
			return;
		
		$arrOrders = array();
		
		while ($objOrders->next())
		{
			$arrOrders[] = array(
				'id' => $objOrders->id,
				'orderno' => $objOrders->orderno,
				'memberName' => $objOrders->memberName ? $objOrders->memberName : 'unknow member',
				'date_add' => substr($objOrders->date_add, 0, 10),
				'total_paid_real' => $this->obj_currency->change_order($objOrders->total_paid_real,$objOrders->iso_code),
				'status' => $objOrders->status,
				//'status' => (($objOrders->status	== 'Payment accepted' && $objOrders->payment == 'COD') ? 'Preparation in progress' : $objOrders->status),			
			);
		}
		
		return $arrOrders; 
	}
	
	/**
	 * Show order details
	 * @params int
	 * @return array - order detail
	 */
	protected function getOrderDetail($id_order=0)
	{
		if (!$id_order)
			return;
		
		$objOrdersCnt = $this->Database->prepare("SELECT odr.`id`
												FROM `tl_orders` AS odr 
												LEFT JOIN `tl_member` AS me ON me.`id`=odr.`id_member` 
												LEFT JOIN `tl_order_detail` AS odd ON odd.`pid`=odr.`id`
												LEFT JOIN `product_catalog` AS cata ON odd.`product_catalog_id`=cata.`id` 
												GROUP BY odr.`id`
												ORDER BY odr.`date_add`")
									->execute(time(), time());
		
		if ($objOrdersCnt->numRows < 1)
			return;
		
		$cnt = $objOrdersCnt->numRows;
		$this->Template->orderCnt = $objOrdersCnt->numRows;
			
		$objOders = $this->Database->prepare("SELECT IF(odr.dly_firstname <> '', odr.dly_firstname, addrbook.firstname) AS firstname, IF(odr.dly_lastname <> '', odr.dly_lastname, addrbook.lastname) AS lastname, IF(odr.dly_street <> '', odr.dly_street, addrbook.street) AS shippingAddr, IF(odr.dly_street_2 <> '', odr.dly_street_2, addrbook.street_2) AS shippingAddr2, IF(odr.dly_city <> '', odr.dly_city, addrbook.city) AS city, IF(odr.dly_state <> '', odr.dly_state, addrbook.state) AS state, IF(odr.dly_country <> '', odr.dly_country, addrbook.country) AS country, IF(odr.dly_postcode <> '', odr.dly_postcode, addrbook.postcode) AS postcode, 
								odd.id AS detail_id, odd.product_price AS odrPrice, odr.payment AS paymethod, odr.total_discounts, odr.orderno AS orderno, odr.total_wrapping, 
								odr.total_catolog, odr.total_paid_real, odr.total_paid, odr.date_add AS placed_time, odr.total_shipping AS shipping_fee, odr.`shipping_id` AS ship_id, 
								odr.total_catolog AS product_fee, odr.`odr_status`, odd.product_attribute AS product_attr, prod_cata.skuid AS skuid, odr.`odr_comments`AS comments, odr.`odr_status_comments`, odr.`odr_can_com`, prod_cata.category AS category, prod_cata.id AS id_catalog, prod_cata.name AS catalogName, 
								odd.product_quantity AS prod_quantity, odd.is_package AS is_wrapping, odd.gwPrice AS wrapping_price, me.`username`, me.`email`, odr.`id_member`, cur.iso_code
								FROM tl_orders AS odr
								LEFT JOIN tl_order_detail AS odd ON odd.pid=odr.id
								LEFT JOIN tl_address_book AS addrbook ON addrbook.id=odr.id_address_delivery
								LEFT JOIN tl_member AS me ON me.id=odr.id_member
								LEFT JOIN product_catalog AS prod_cata ON prod_cata.id=odd.product_catalog_id
								LEFT JOIN tl_currency AS cur ON cur.id=odr.id_currency 
								WHERE odr.id=?")
						->execute($id_order);
		if ($objOders->numRows < 1)
			return;
		
		$arrOrderdetails = array();
		
		$index = 0;
		while ($objOders->next())
		{
			$shippingFree = $objOders->shipping_fee;
			$total_discounts = $objOders->total_discounts;
			
			if (strpos($objOders->product_attr,"\r\n") !== false)
				$arrColor = explode("\r\n", $objOders->product_attr);
			else
				$arrColor = explode("\n", $objOders->product_attr);
			
			$arrColor_bk = explode(":", $arrColor[0]);
			$strColor = $arrColor_bk[1];
			$arrSize_bk = explode(":", $arrColor[1]);
			$strSize = $arrSize_bk[1];
			
			$objCatalogSize = $this->Database->prepare("SELECT `id` FROM `catalog_size` WHERE `size`=?")
								->execute($strSize);
								
			$objCatalogColor = $this->Database->prepare("SELECT `id` FROM `catalog_colour` WHERE `title`=?")
								->execute($strColor);
			
			$imgPath = "daft_files/catalog/products/" . $objOders->skuid . "/$strColor/1.jpg";
			
			$arrOrderdetails[$index] = array(
				'firstname' => $objOders->firstname,
				'lastname' => $objOders->lastname,
				'shippingAddr' => $objOders->shippingAddr,
				'shippingAddr2' => $objOders->shippingAddr2,
				'city' => $objOders->city,
				'state' => $objOders->state,
				'country' => $objOders->country,
				'postcode' => $objOders->postcode,
				'odrPrice' => $objOders->odrPrice,
				'paymethod' => $objOders->paymethod,
				'total_discounts' => $objOders->total_discounts,
				'orderno' => $objOders->orderno,
				'total_wrapping' => $objOders->total_wrapping,
				'total_catolog' => $objOders->total_catolog,
				'total_paid_real' => $objOders->total_paid_real,
				'total_paid' => $objOders->total_paid,
				'placed_time' => $objOders->placed_time,
				'shipping_fee' => $objOders->shipping_fee,
				'product_fee' => $objOders->product_fee,
				'product_attr' => $objOders->product_attr,
				'skuid' => $objOders->skuid,
				//'stock' => $objOders->stock,
				'category' => $objOders->category,
				'id_catalog' => $objOders->id_catalog,
				'catalogName' => $objOders->catalogName,
				'prod_quantity' => $objOders->prod_quantity,
				'is_wrapping' => $objOders->is_wrapping,
				'wrapping_price' => $objOders->wrapping_price,
				'id_color' => $objCatalogColor->id,
				'colorName' => $strColor,
				'id_size' => $objCatalogSize->id,
				'sizeName' => $strSize,
				'imagePath' => $imgPath,
				'shippingFree' => $shippingFree,
				'total_discounts' => $total_discounts,
				//'odr_status' => (($objOders->odr_status	== '1' && $objOders->paymethod == 'COD') ? '2' : $objOders->odr_status),
				'odr_status' => $objOders->odr_status,
				'id_order' => $id_order,
				'username' => $objOders->username,
				'email' => $objOders->email,
				'id_member' => $objOders->id_member,
				'comments' => $objOders->comments,
				'odr_status_comments'=> $objOders->odr_status_comments,
				'odr_can_com' => $objOders->odr_can_com,
				'order_detail_id' => $objOders->detail_id,
				'iso_code' => $objOders->iso_code,
			);
			/** get all the colour and size of product **/
			$obj_colours = $this->Database->prepare("SELECT cata_co.`title`,cata_co.`id`  
													FROM `tl_catalog_attribute` AS cata_attr 
													INNER JOIN `catalog_colour` AS cata_co ON cata_co.`id`=cata_attr.`id_color` 
													WHERE cata_attr.`id_catalog`=? GROUP BY cata_co.`title`")
											->execute($objOders->id_catalog);
			if ($obj_colours->numRows > 0){
				while ($obj_colours->next())
					$arrOrderdetails[$index]['colours'][$obj_colours->id] = $obj_colours->title;
			}
			
			$obj_sizes = $this->Database->prepare("SELECT cata_size.`size`,cata_size.`id`  
													FROM `tl_catalog_attribute` AS cata_attr 
													INNER JOIN `catalog_size` AS cata_size ON cata_size.`id`=cata_attr.`id_size` 
													WHERE cata_attr.`id_catalog`=? AND cata_attr.`id_color`=? GROUP BY cata_size.`size`")
											->execute($objOders->id_catalog, $objCatalogColor->id);
			if ($obj_sizes->numRows > 0){
				while ($obj_sizes->next())
					$arrOrderdetails[$index]['sizes'][$obj_sizes->id] = $obj_sizes->size;
			}
			
			if ($objCatalogSize->id)
			{
				$obj_quantity = $this->Database->prepare("SELECT `catalog_stock`   
														FROM `tl_catalog_attribute` 
														WHERE `tl_catalog_attribute`.`id_catalog`=? AND `tl_catalog_attribute`.`id_color`=? AND `tl_catalog_attribute`.`id_size`=?")
												->execute($objOders->id_catalog, $objCatalogColor->id, $objCatalogSize->id);
												
				if ($obj_quantity->numRows > 0){
					if ($obj_quantity->next())
						$arrOrderdetails[$index]['catalog_stock'] = $obj_quantity->catalog_stock;
				}
			}
			
			if ($objOders->ship_id)
			{
				$obj_shippings = $this->Database->prepare("SELECT * FROM `tl_shippings` WHERE `id`=?")
												->execute($objOders->ship_id);
				$arrOrderdetails[$index]['shippings'] = array(
					'name'=>$obj_shippings->name,
					'ship_des'=>$obj_shippings->ship_des,
				);
			}
			
			$index++;
		}
		
		return $arrOrderdetails;
	}
}

?>